﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist_xzwk]
(
    @CompanyId int,
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),	
	@StartIndex int,
	@EndIndex INT
)

AS
	/*;WITH list As(
		
--Set @IAF=ISNULL((Select Sum(Prices) From Order_PaymentRecord Where CompanyId=@companyId 
--and Convert(nchar(10),FinanceDate,120)=@CreateDate and SWId=2),0)
SELECT ROW_NUMBER() OVER (ORDER BY OId)AS Row,* from (select sum(Prices) AS Prices,cusname,oid FROM (
SELECT 
id,CusName, Prices
,OId FROM Order_PaymentRecord  Where  companyId=@CompanyId AND  Convert(nchar(10),FinanceDate,120)
 BETWEEN @StartDate and @EndDate and SWId=2 AND oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
	UNION 
	--AND oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
SELECT 0,cusname, dch_wk,OId FROM Order_Change_Hedge WHERE CompanyId=@companyId 
AND oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )

and Convert(nchar(10),UpTime,120)  BETWEEN @StartDate and @EndDate AND dch_wk!=0 ) AS ab GROUP BY cusname,oid ) AS nb
	)
	
Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(Prices) FROM list) AS DataSum 
From list Where Row between @StartIndex and @EndIndex  
*/

Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')
	

;WITH list As(
		
--Set @IAF=ISNULL((Select Sum(Prices) From Order_PaymentRecord Where CompanyId=@companyId 
--and Convert(nchar(10),FinanceDate,120)=@CreateDate and SWId=2),0)
SELECT ROW_NUMBER() OVER (ORDER BY OId)AS Row,* from (select sum(Prices) AS Prices,cusname,oid FROM (
SELECT 
Order_PaymentRecord.id,Order_PaymentRecord.CusName, Order_PaymentRecord.Prices
,Order_PaymentRecord.OId FROM Order_PaymentRecord inner join [Order] o  on Order_PaymentRecord.oid =o.oid and 
 Order_PaymentRecord.companyId=@CompanyId AND o.NOE_Flag<>2 AND o.companyId=@CompanyId  and SWId=2 
  AND  FinanceDate >= @sRq and FinanceDate<=@eRq

 --and left(o.AuditDate,10)<>left(FinanceDate,10)
 --2014-12-30取消此条件，尾款全部按尾款计算
	UNION all
	
SELECT 0,Order_Change_Hedge.cusname, Order_Change_Hedge.dch_wk,Order_Change_Hedge.OId FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.CompanyId=@companyId and Order_Change_Hedge.oid =o.oid   
AND o.companyId=@CompanyId AND o.NOE_Flag<>2 

and UpTime >= @sRq and UpTime<=@eRq AND dch_wk!=0 
) 
AS ab GROUP BY cusname,oid ) AS nb
	)
	
Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(Prices) FROM list) AS DataSum 
From list Where Row between @StartIndex and @EndIndex  
	
	RETURN
